using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;
using FluentNHibernate.Automapping;
using FluentNHibernate.Cfg;
using Microsoft.Win32;
using Environment = NHibernate.Cfg.Environment;

namespace NHibernateInsight.Tests.Infrastructure.Data
{
    public class Sql2005DatabaseUsageStrategy : DatabaseUsageStrategyBase
    {
        public Sql2005DatabaseUsageStrategy(string databaseName)
            : this(databaseName, _DefaultSqlServerName)
        {}

        public Sql2005DatabaseUsageStrategy(string databaseName, string sqlServerName)
            : base(databaseName)
        {
            SqlServerName = sqlServerName;
        }

        private static string _DefaultSqlServerName
        {
            get
            {
                return "(local)";
            }
        }

        protected string SqlServerName
        {
            get
            {
                return _sqlServerName;
            }
            set
            {
                _sqlServerName = value;
            }
        }
        private string _sqlServerName;

        protected virtual string SqlServerInstanceKeyInRegistry
        {
            get
            {
                return "SQL2005";
            }
        }

        public override void PrepareDatabaseMedia()
        {
            var sqlServerDataDirectory = _GetSqlServerDataDirectory();
            var newLIne = System.Environment.NewLine;

            var scriptBuilder = new StringBuilder();
            scriptBuilder.AppendFormat("    if (select db_id('{0}')) is null {1}", DatabaseName, newLIne);
            scriptBuilder.AppendFormat("    create database {0} {1}", DatabaseName, newLIne);
            scriptBuilder.AppendFormat("    on primary {0}", newLIne);
            scriptBuilder.AppendFormat("    (name = {0}_Data, {1}", DatabaseName, newLIne);
            scriptBuilder.AppendFormat("     filename = '{0}{1}.mdf', {2}", sqlServerDataDirectory, DatabaseName, newLIne);
            scriptBuilder.AppendFormat("     size = 5MB, {0}", newLIne);
            scriptBuilder.AppendFormat("     filegrowth = 10) {0}", newLIne);
            scriptBuilder.AppendFormat("    log on {0}", newLIne);
            scriptBuilder.AppendFormat("    (name = {0}_Log, {1}", DatabaseName, newLIne);
            scriptBuilder.AppendFormat("     filename = '{0}{1}.ldf', {2}", sqlServerDataDirectory, DatabaseName, newLIne);
            scriptBuilder.AppendFormat("     size = 1MB, {0}", newLIne);
            scriptBuilder.AppendFormat("     filegrowth = 5) {0}", newLIne);
            scriptBuilder.AppendFormat("{0}", newLIne);
            scriptBuilder.AppendFormat("{0}", newLIne);

            _ExecuteScript(_ConnectionStringFor("master"), scriptBuilder.ToString());


            if (HideCreateAndDropDbSql == false) Console.Write(scriptBuilder.ToString());

            HasDatabaseBeenCreated = true;
        }

        public override void DropDatabaseMedia()
        {
            if (HasDatabaseBeenCreated == false) return;

            var newLIne = System.Environment.NewLine;

            var scriptBuilder = new StringBuilder();
            scriptBuilder.AppendFormat("{0}", newLIne);
            scriptBuilder.AppendFormat("{0}", newLIne);
            scriptBuilder.AppendFormat("    alter database {0} set single_user with rollback immediate{1}", DatabaseName, newLIne);
            scriptBuilder.AppendFormat("{0}", newLIne);
            scriptBuilder.AppendFormat("    drop database {0}{1}", DatabaseName, newLIne);

            _ExecuteScript(_ConnectionStringFor("master"), scriptBuilder.ToString());


            if (HideCreateAndDropDbSql == false) Console.Write(scriptBuilder.ToString());
        }

        public override IDictionary<string, string> ConfigurationProperties
        {
            get
            {
                var properties = new Dictionary<string, string>();
                properties.Add(Environment.ConnectionDriver, "NHibernate.Driver.SqlClientDriver");
                properties.Add(Environment.Dialect, "NHibernate.Dialect.MsSql2005Dialect");
                properties.Add(Environment.ConnectionProvider, "NHibernate.Connection.DriverConnectionProvider");
                properties.Add(Environment.ConnectionString, _ConnectionStringFor(DatabaseName));
                properties.Add(Environment.ShowSql, "true");
                properties.Add(Environment.ReleaseConnections, "on_close");
                properties.Add(Environment.ProxyFactoryFactoryClass, "NHibernate.ByteCode.Castle.ProxyFactoryFactory, NHibernate.ByteCode.Castle");
                //by specifying a default schema, nhibernate's dynamic sql queries benefit from caching
                properties.Add(Environment.DefaultSchema, string.Format("{0}.dbo", DatabaseName));

                return properties;
            }
        }

        private string _ConnectionStringFor(string databaseName)
        {
            return string.Format("Server={0};initial catalog={1};Integrated Security=SSPI", SqlServerName,
                                 databaseName);
        }
        private string _GetSqlServerDataDirectory()
        {
            var sqlServerRegKey = @"SOFTWARE\Microsoft\Microsoft SQL Server\";
            var sqlServerInstanceName = (string)Registry.LocalMachine
                                                    .OpenSubKey(sqlServerRegKey + @"Instance Names\SQL")
                                                    .GetValue(SqlServerInstanceKeyInRegistry);

            var sqlServerInstanceSetupRegKey = string.Format("{0}{1}{2}", sqlServerRegKey,
                                                             sqlServerInstanceName,
                                                             @"\Setup");
            return (string)Registry.LocalMachine
                               .OpenSubKey(sqlServerInstanceSetupRegKey)
                               .GetValue("SQLDataRoot") + @"\Data\";
        }
        private void _ExecuteScript(string connectionString, string sqlScript)
        {
            using (var conn = new SqlConnection(connectionString))
            {
                using (var command = new SqlCommand(sqlScript, conn))
                {
                    conn.Open();
                    command.ExecuteNonQuery();
                }
            }
        }
    }
}